package com.reverse.engineering.util.core.run.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import com.ApplicationProperties;
import com.reverse.engineering.util.core.run.bean.DatabaseProperties;
import com.reverse.engineering.util.core.run.bean.database.Database;
import com.reverse.engineering.util.core.run.bean.database.Field;
import com.reverse.engineering.util.core.run.bean.database.Table;
import com.reverse.engineering.util.core.run.enums.ExcelField;
import com.reverse.engineering.util.core.run.enums.FieldEquals;

/**
 * 负责读取Excel
 * 
 * @author liu
 * @date 2020-08-10
 */
public class ReadExcel {

    private static final String EXTENSION_XLS = "xls";
    private static final String EXTENSION_XLSX = "xlsx";

    /**
     * 读取Excel获取每个数据库表对象
     * @throws IOException 
     * @throws FileNotFoundException 
     */
    public static ArrayList<Database> readExcel() throws Exception {
        // 初始整个Excel
        InputStream inputStream = new ClassPathResource(ApplicationProperties.excelPath).getInputStream();
        Workbook workbook = null;
        if (EXTENSION_XLS
            .equals(ApplicationProperties.excelPath.substring(ApplicationProperties.excelPath.lastIndexOf(".") + 1))) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (EXTENSION_XLSX
            .equals(ApplicationProperties.excelPath.substring(ApplicationProperties.excelPath.lastIndexOf(".") + 1))) {
            workbook = new XSSFWorkbook(inputStream);
        }

        Sheet sheet = workbook.getSheetAt(0);
        getDatabaseLine(sheet);
        getTableLine(sheet);
        removeTableRepetition();
        getFieldLine(sheet);
        initTables(sheet);
        removeFieldRepetition();
        removeFieldPropertiesRepetition();
        fieldSort();
        removeSpecialRepetition();
        verify();
        createSort();
        return null;
    }

    /**
     * 验证数据的正确性
     * @throws Exception 
     */
    private static void verify() throws Exception {
        ArrayList<Table> tables = Database.tables;
        ArrayList<Table> arrayList = new ArrayList<>();
        for (Table table : tables) {
            arrayList.add(table);
            // 查看是否有重复的外键名
            ArrayList<String> referencesComments = new ArrayList<>();
            for (Field field : table.getFields()) {
                String referencesTable = field.getReferencesTable();
                String referencesField = field.getReferencesField();
                String referencesComment = field.getReferencesComment();
                if (referencesTable == null || "".equals(referencesTable.trim()) || referencesField == null
                    || "".equals(referencesField.trim())) {
                    field.setReferencesTable(null);
                    field.setReferencesField(null);
                    field.setReferencesComment(null);
                } else {
                    if (referencesComment == null || "".equals(referencesComment.trim())) {
                        field.setReferencesComment(table.getTableName() + "_" + field.getFieldName());
                    }
                    referencesComment = field.getReferencesComment();
                    for (String referencesCommentList : referencesComments) {
                        if (referencesComment.equalsIgnoreCase(referencesCommentList)) {
                            throw new Exception(table.getTableName() + "外键名重复,大小写也不能相同");
                        }
                    }
                    referencesComments.add(referencesComment);
                }
            }
        }
        Database.tables = arrayList;
    }

    /**
     * 对创建顺序进行排序(先建没有外键的表,有外键的表先建被关联的表)
     */
    private static void createSort() {
        // 有外键的字段
        ArrayList<Table> arrayList = new ArrayList<>();
        while (true) {
            if (!createSort(arrayList)) {
                break;
            }
        }
        Database.tables = arrayList;
    }

    /**
     * 对创建顺序进行排序(先建没有外键的表,有外键的表先建被关联的表)
     * @return 
     */
    private static boolean createSort(ArrayList<Table> arrayList) {
        // 标记是否有外键
        boolean exists = false;
        ArrayList<Table> tables = Database.tables;
        if (tables.size() == arrayList.size()) {
            return exists;
        }
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            // 外键的外键表是否已创建
            boolean fieldExists = true;
            // 遍历所有字段
            for (Field field : fields) {
                // 判断字段是否有外键
                if (field.getReferencesTable() != null) {
                    // 标记外键表是否存在
                    boolean tableExists = false;
                    // 判断外键表是否存在
                    for (Table tableTwo : tables) {
                        if (field.getReferencesTable().equalsIgnoreCase(tableTwo.getTableName())) {
                            tableExists = true;
                            break;
                        }
                    }
                    // 如果外键表不存在就判断下一个字段
                    if (!tableExists) {
                        continue;
                    }
                    tableExists = false;
                    // 如果外键表存在就判断表是否已添加到集合中
                    for (Table tableTwo : arrayList) {
                        if (field.getReferencesTable().equalsIgnoreCase(tableTwo.getTableName())) {
                            tableExists = true;
                            break;
                        }
                    }
                    // 如果表已添加到集合中就判断下一个字段
                    if (tableExists) {
                        continue;
                    }
                    exists = true;
                    fieldExists = false;
                }
            }
            // 外键表已创建
            if (fieldExists && !arrayList.contains(table)) {
                arrayList.add(table);
            }
        }
        return exists;
    }

    /**
     * 特殊字段去除重复
     * 
     * @param sheet
     */
    private static void removeSpecialRepetition() {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            removeSpecialRepetition(table);
        }
    }

    /**
     * 特殊字段去除重复(防止一个字段既是ID又是标记删除)
     * 
     * @param sheet
     */
    private static void removeSpecialRepetition(Table table) {
        String idName = table.getIdName();
        String serialNumberName = table.getSerialNumberName();
        String delName = table.getDelName();
        if (serialNumberName != null && (serialNumberName.equals(idName) || serialNumberName.equals(delName))) {
            table.setExistSerialNumber(false);
            table.setSerialNumberName(null);
        }
        if (delName != null && delName.equals(idName)) {
            table.setExistDel(false);
            table.setDelName(null);
        }
    }

    /**
     * 获取数据库名的那一行,如果有多行只获取第一个
     */
    public static void getDatabaseLine(Sheet sheet) {
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            String cellDatabaseName = cellGetValue(row.getCell(ExcelField.DATABASE_NAME.getLineNumber()));
            if (StringUtils.isNotBlank(cellDatabaseName)) {
                // ================================================================
                Database.databaseName = cellDatabaseName;
                // ================================================================
                return;
            }
        }
    }

    /**
     * 获取数据表名的行
     */
    public static void getTableLine(Sheet sheet) {
        ArrayList<Table> arrayList = new ArrayList<>();
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            String cellTableName = cellGetValue(row.getCell(ExcelField.TABLE_NAME.getLineNumber()));
            if (StringUtils.isNotBlank(cellTableName)) {
                // ================================================================
                String cellTableComment = cellGetValue(row.getCell(ExcelField.TABLE_COMMENT.getLineNumber()));
                String cellTableOperation = cellGetValue(row.getCell(ExcelField.TABLE_OPERATION.getLineNumber()));
                String cellTableStatic = cellGetValue(row.getCell(ExcelField.TABLE_STATIC.getLineNumber()));
                String cellTableAutoIncrement = cellGetValue(row.getCell(ExcelField.IS_AUTO_INCREMENT.getLineNumber()));

                Table table = new Table();
                table.setTableName(cellTableName);
                table.setExcelLineNumber(rowIndex);
                if (StringUtils.isNotBlank(cellTableComment)) {
                    table.setTableComment(cellTableComment);
                }
                if (StringUtils.isNotBlank(cellTableOperation)) {
                    table.setOperation(cellTableOperation);
                }
                if (StringUtils.isNotBlank(cellTableStatic)) {
                    table.setTableStatic(cellTableStatic);
                }
                if (StringUtils.isNotBlank(cellTableAutoIncrement)) {
                    table.setExistAutoIncrement(true);
                }

                arrayList.add(table);
                // ================================================================
            }
        }
        Database.tables = arrayList;
    }

    /**
     * 对表进行去重
     */
    private static void removeTableRepetition() {
        ArrayList<Table> tables = Database.tables;
        ArrayList<Table> arrayList = new ArrayList<>();
        for (Table table : tables) {
            // 标记是否存在
            boolean exists = false;
            for (Table tableList : arrayList) {
                if (table.getTableName().equalsIgnoreCase(tableList.getTableName())) {
                    exists = true;
                    break;
                }
            }
            if (!exists) {
                arrayList.add(table);
            }
        }
        Database.tables = arrayList;
    }

    /**
     * 获取字段的行
     */
    public static void getFieldLine(Sheet sheet) {
        ArrayList<Table> tables = Database.tables;
        Table table = null;
        int size = tables.size();
        int lastRowNum = sheet.getLastRowNum();
        int excelLineNumber = 0;
        int lastLine = 0;
        for (int i = 0; i < size; i++) {
            table = tables.get(i);
            if (i == size - 1) {
                lastLine = lastRowNum + 1;
            } else {
                lastLine = tables.get(i + 1).getExcelLineNumber();
            }
            excelLineNumber = table.getExcelLineNumber();
            if (excelLineNumber + 1 > lastRowNum) {
                return;
            }
            ArrayList<Field> arrayList = new ArrayList<>();
            for (int rowIndex = excelLineNumber + 1; rowIndex < lastLine; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                String cellFieldName = cellGetValue(row.getCell(ExcelField.FIELD_NAME.getLineNumber()));
                if (StringUtils.isNotBlank(cellFieldName)) {
                    // ================================================================
                    String cellFieldType = cellGetValue(row.getCell(ExcelField.FIELD_TYPE.getLineNumber()));
                    String cellFieldLength = cellGetValue(row.getCell(ExcelField.FIELD_LENGTH.getLineNumber()));
                    String cellFieldProperties = cellGetValue(row.getCell(ExcelField.FIELD_PROPERTIES.getLineNumber()));
                    String cellFieldForeignKeyTable =
                        cellGetValue(row.getCell(ExcelField.FOREIGN_KEY_TABLE.getLineNumber()));
                    String cellFieldForeignKeyField =
                        cellGetValue(row.getCell(ExcelField.FOREIGN_KEY_FIELD.getLineNumber()));
                    String cellFieldForeignKeyComment =
                        cellGetValue(row.getCell(ExcelField.FOREIGN_KEY_COMMENT.getLineNumber()));
                    String fieldComment = cellGetValue(row.getCell(ExcelField.FIELD_COMMENT.getLineNumber()));
                    String cellFieldComment =
                        fieldComment != null && !"".equals(fieldComment) ? fieldComment : cellFieldName;
                    String cellFieldOperation = cellGetValue(row.getCell(ExcelField.FIELD_OPERATION.getLineNumber()));
                    String cellFieldIsId = cellGetValue(row.getCell(ExcelField.FIELD_IS_ID.getLineNumber()));
                    String cellFieldIsSerialNumber =
                        cellGetValue(row.getCell(ExcelField.FIELD_IS_SERIAL_NUMBER.getLineNumber()));
                    String cellFieldIsDel = cellGetValue(row.getCell(ExcelField.FIELD_IS_DEL.getLineNumber()));

                    Field field = new Field();
                    field.setFieldName(cellFieldName);
                    if (StringUtils.isNotBlank(cellFieldType)) {
                        field.setFieldType(cellFieldType);
                    }
                    if (StringUtils.isNotBlank(cellFieldProperties)) {
                        field.setFieldProperty(cellFieldProperties.split("/"));
                    }
                    if (StringUtils.isNotBlank(cellFieldForeignKeyTable)) {
                        field.setReferencesTable(cellFieldForeignKeyTable);
                    }
                    if (StringUtils.isNotBlank(cellFieldForeignKeyField)) {
                        field.setReferencesField(cellFieldForeignKeyField);
                    }
                    if (StringUtils.isNotBlank(cellFieldForeignKeyComment)) {
                        field.setReferencesComment(cellFieldForeignKeyComment);
                    }
                    if (StringUtils.isNotBlank(cellFieldComment)) {
                        field.setFieldComment(cellFieldComment);
                    }
                    if (StringUtils.isNotBlank(cellFieldOperation)) {
                        field.setOperation(cellFieldOperation);
                    }
                    for (FieldEquals fieldEquals : FieldEquals.values()) {
                        if (field.getFieldType().equalsIgnoreCase(fieldEquals.getDatabase())) {
                            field.setFieldEquals(fieldEquals);
                        }
                    }
                    if (StringUtils.isNotBlank(cellFieldLength) && field.getFieldEquals().getAllowLength()) {
                        field.setFieldLength(Integer.parseInt(cellFieldLength));
                    }

                    if (table.getExistId() == null) {
                        if (StringUtils.isNotBlank(cellFieldIsId)) {
                            table.setExistId(true);
                            table.setIdName(cellFieldName);
                        }
                    }
                    if (table.getSerialNumberName() == null) {
                        if (StringUtils.isNotBlank(cellFieldIsSerialNumber)) {
                            table.setExistSerialNumber(true);
                            table.setSerialNumberName(cellFieldName);
                        }
                    }
                    if (table.getDelName() == null) {
                        if (StringUtils.isNotBlank(cellFieldIsDel)) {
                            table.setExistDel(true);
                            table.setDelName(cellFieldName);
                        }
                    }

                    arrayList.add(field);
                    // ================================================================
                }
            }
            table.setFields(arrayList);
        }
    }

    /**
     * 对字段进行去重
     * 
     * @param arrayList
     * @return
     */
    private static void removeFieldRepetition() {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            ArrayList<Field> arrayList = new ArrayList<>();
            for (Field field : fields) {
                // 标记是否存在
                boolean exists = false;
                for (Field fieldList : arrayList) {
                    if (field.getFieldName().equalsIgnoreCase(fieldList.getFieldName())) {
                        exists = true;
                    }
                }
                if (!exists) {
                    arrayList.add(field);
                }
            }
            table.setFields(arrayList);
        }
    }

    /**
     * 对字段的属性进行去重
     * @throws Exception 
     */
    private static void removeFieldPropertiesRepetition() throws Exception {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            int primarykeyCount = 0;
            int autoIncrementCount = 0;
            for (Field field : fields) {
                String[] fieldPropertys = field.getFieldProperty();
                ArrayList<String> arrayList = new ArrayList<>();
                if (fieldPropertys != null) {
                    for (String fieldProperty : fieldPropertys) {
                        for (String fieldPropertyList : field.getFieldEquals().getAllowProperty()) {
                            if (fieldProperty.toLowerCase().contains(fieldPropertyList.toLowerCase())) {
                                // 标记是否存在
                                boolean exists = false;
                                for (String arrays : arrayList) {
                                    if (arrays.contains(fieldPropertyList)) {
                                        exists = true;
                                        break;
                                    }
                                }
                                if (!exists) {
                                    arrayList.add(fieldProperty);
                                }
                            }
                        }
                    }
                    Object[] array = arrayList.toArray();
                    String[] str = new String[array.length];
                    for (int i = 0; i < array.length; i++) {
                        str[i] = array[i].toString();
                    }
                    field.setFieldProperty(str);

                    for (String fieldProperty : str) {
                        if (fieldProperty.equalsIgnoreCase(DatabaseProperties.getPrimarykey())) {
                            primarykeyCount++;
                        }
                        if (fieldProperty.equalsIgnoreCase(DatabaseProperties.getAutoIncrement())) {
                            autoIncrementCount++;
                            // 标记是否有主键
                            boolean exists = false;
                            for (String fieldPropertyTwo : fieldPropertys) {
                                if (fieldPropertyTwo.equalsIgnoreCase(DatabaseProperties.getPrimarykey())) {
                                    exists = true;
                                    break;
                                }
                            }
                            if (!exists) {
                                throw new Exception(table.getTableName() + "的自动增长字段不是主键或ID");
                            }
                        }
                    }
                }
            }
            if (primarykeyCount > 1 || autoIncrementCount > 1) {
                throw new Exception(table.getTableName() + "的主键或自动增长属性只能有一个,如果确实只有一个,那就是因为程序自动给ID字段也加了一个,请修改程序");
            }
        }
    }

    /**
     * 对字段进行排序
     * 
     * @param arrayList
     * @return
     */
    private static void fieldSort() {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            ArrayList<Field> arrayList = new ArrayList<>();
            for (Field field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getFieldName())) {
                    arrayList.add(field);
                    break;
                }
            }
            for (Field field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getFieldName())) {
                    continue;
                }
                arrayList.add(field);
            }
            table.setFields(arrayList);
        }
    }

    /**
     * 从单元格获取数据
     * 
     * @param cell
     * @return
     */
    public static String cellGetValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        switch (cell.getCellType()) {
            case BLANK:
                return null;
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((int)cell.getNumericCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case ERROR:
                return cell.getRichStringCellValue().getString();
            case _NONE:
                return cell.getRichStringCellValue().getString();
            default:
                return cell.getRichStringCellValue().getString();
        }
    }

    /**
     * 初始化表的关键字段,如ID
     * 
     * @param sheet
     */
    private static void initTables(Sheet sheet) {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            for (Field field : fields) {
                String fieldName = field.getFieldName();
                if (table.getIdName() == null) {
                    if (ApplicationProperties.id.equalsIgnoreCase(fieldName)) {
                        table.setExistId(true);
                        table.setIdName(fieldName);
                        String[] fieldProperty = field.getFieldProperty();
                        if (fieldProperty == null) {
                            fieldProperty = new String[0];
                        }
                        String[] str = new String[fieldProperty.length + 2];
                        for (int i = 0; i < fieldProperty.length; i++) {
                            str[i] = fieldProperty[i];
                        }
                        str[fieldProperty.length] = DatabaseProperties.getPrimarykey();
                        str[fieldProperty.length + 1] = DatabaseProperties.getAutoIncrement();
                        field.setFieldProperty(str);
                    }
                }
                if (table.getIdName() == null) {
                    String[] fieldProperty = field.getFieldProperty();
                    for (String property : fieldProperty) {
                        if (DatabaseProperties.getAutoIncrement().equalsIgnoreCase(property)) {
                            table.setExistId(true);
                            table.setIdName(fieldName);
                        }
                    }
                }
                if (table.getSerialNumberName() == null) {
                    if (ApplicationProperties.serial_number.equalsIgnoreCase(fieldName)) {
                        table.setExistSerialNumber(true);
                        table.setSerialNumberName(fieldName);
                    }
                }
                if (table.getDelName() == null) {
                    if (ApplicationProperties.is_del.equalsIgnoreCase(fieldName)) {
                        table.setExistDel(true);
                        table.setDelName(fieldName);
                    }
                }
            }
        }
    }

}